	package browser.service;

	import browser.dao.UserInfo;
	import java.io.*;
	import java.sql.*;
	import java.util.ArrayList;
	import javax.servlet.*;
	import javax.servlet.http.*;

	/**
	 * Servlet that will display update or delete page.
	 *
	 */
	public class ModifyRow extends HttpServlet {

		public static Connection conn;
		public static Statement stmt;

		public void doGet(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			// doPost(request,response);
			response.setContentType("text/html");
			PrintWriter out = response.getWriter();

			// Check if session is expired
			if (request.getSession(false) == null
					|| (Boolean) request.getSession(false).getAttribute(
							"expired") == true) {
				out.println("Session expired");
				return;
			}

			String[] uid = request.getParameterValues("uid");
			String[] uacc = request.getParameterValues("uAccess");
			String[] tname = request.getParameterValues("tName");
			String[] submitbutton = request.getParameterValues("submitbutton");
			String conf = request.getParameter("confirm");
			String sql = request.getParameter("sql");

			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} catch (ClassNotFoundException e) {
				out.println(e.getMessage());
				return;
			}

			Connection conn = null;
			UserInfo user = MyUtilities.getUserInfo(uid[0]);

			if ((user.getUaccess() == null)
					|| (!uacc[0].equals(user.getUaccess()))) {
				return;
			}
			try {
				conn = DriverManager.getConnection("jdbc:oracle:thin:"
						+ user.getUid() + "/" + user.getPassword()
						+ "@oraserver.cs.luc.edu:1521:cs");
			} catch (SQLException e) {
				out.println(e.getMessage());
				return;
			}
			if (conn == null) {
				return;
			}
			Statement stmt = null;
			try {
				stmt = conn.createStatement();
			} catch (SQLException e) {
				try {
					conn.close();
				} catch (SQLException e2) {
				};
				out.println(e.getMessage());
				return;
			}

			if (submitbutton[0].equals("delete")) {
				if (conf != null && conf.equals("Y")) {
					try {
						stmt.executeUpdate(sql);
					} catch (SQLException e) {
						e.printStackTrace();
						out.println(e.getMessage());
					}
				}
			} else if (submitbutton[0].equals("update")) {
				updateRow(stmt, out, uid, uacc, tname[0], sql);
			}

			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			out.close();
		}

		public void doPost(HttpServletRequest request,
				HttpServletResponse response) throws ServletException,
				IOException {

			response.setContentType("text/html");
			// response.setContentType("application/vnd.ms-excel");
			PrintWriter out = response.getWriter();

			String[] uid = request.getParameterValues("uid");
			String[] uacc = request.getParameterValues("uAccess");
			String[] tname = request.getParameterValues("tName");
			String[] submitbutton = request.getParameterValues("submitbutton");
			String sql = request.getParameter("sql");

			try {
				Class.forName("oracle.jdbc.driver.OracleDriver");
			} catch (ClassNotFoundException e) {
				out.println("Error loading the Driver:" + e.getMessage());
				return;
			}

			Connection conn = null;
			UserInfo user = MyUtilities.getUserInfo(uid[0]);

			if ((user.getUaccess() == null)
					|| (!uacc[0].equals(user.getUaccess()))) {
				out.print("Please login first");
				return;
			}
			try {
				conn = DriverManager.getConnection("jdbc:oracle:thin:"
						+ user.getUid() + "/" + user.getPassword()
						+ "@oraserver.cs.luc.edu:1521:cs");
			} catch (SQLException e1) {
				out.println("Error connecting to Oracle:" + e1.getMessage());
				return;
			}
			if (conn == null) {
				out.println("Null Connection");
				return;
			}
			Statement stmt = null;
			try {
				stmt = conn.createStatement();
			} catch (SQLException e) {
				out.println("createStatement " + e.getMessage());
				try {
					conn.close();
				} catch (SQLException e2) {
				}
				;
				return;
			}

			if (submitbutton[0].equals("insert"))
				insertRow(stmt, out, uid, uacc, tname[0]);

			try {
				stmt.close();
				conn.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
			out.close();
		}

		private void insertRow(Statement stmt, PrintWriter out, String[] uid,
				String[] uacc, String tname) {

			out.println("<html>");
			out.println("  <head><title>Insert");
			out.println("  </title><link type=\"text/css\" rel=\"stylesheet\" href=\"main.css\"/></head>");
			out.println("  <body>");
			out.println("    <p>");
			out.println("    <center>");

			ResultSet rset = null;
			String sql = "select * from " + tname;
			try {
				rset = stmt.executeQuery(sql);
			} catch (SQLException e) {
				out.println("executeQuery " + e.getMessage());
				return;
			}

			try {
				out.println("<form action=\"/DatabaseBrowser/Insert\" method=\"post\">");
				out.println("<input type=\"hidden\" name=\"uid\" value=\""
						+ uid[0] + "\">");
				out.println("<input type=\"hidden\" name=\"uAccess\" value=\""
						+ uacc[0] + "\">");
				out.println("<input type=\"hidden\" name=\"tName\" value=\""
						+ tname + "\">");
				out.println("<table border=2 id=\"table\">");

				ResultSetMetaData meta = rset.getMetaData();

				for (int i = 1; i <= meta.getColumnCount(); i++) {
					out.println("<tr>");

					String temps = "";
					if (meta.getColumnTypeName(i).equals("NUMBER")) {
						if (meta.getPrecision(i) == 0)
							temps = "";
						else if (meta.getScale(i) != 0)
							temps = "(" + meta.getPrecision(i) + ","
									+ meta.getScale(i) + ")";
						else
							temps = "(" + meta.getPrecision(i) + ")";
					} else if (meta.getColumnTypeName(i).equals("DATE")) {
						temps = "";
					} else {
						temps = "(" + meta.getPrecision(i) + ")";
					}

					out.println("<td>" + meta.getColumnLabel(i) + " "
							+ meta.getColumnTypeName(i) + "" + temps + "</td>");
					out.println("<td>");
					out.println("<input type=\"text\" name=\""
							+ meta.getColumnLabel(i) + "\" value=\"\" />");
					out.println("</td>");
					out.println("</tr>");
				}

				out.println("</table>");

			} catch (SQLException e) {
				e.printStackTrace();
			}

			out.println("</br>");

			out.println("<input type=\"submit\" value=\"Insert\">");
			out.println("</form>");

			out.println("</br>");
			out
					.println("<span id=\"note\">(Date format must be \"yyyy-MM-dd HH:mm:ss\". Use \"NOW\" representing current time)</span>");

			out.println("    </center>");
			out.println("    </p>");
			out.println("  </body>");
			out.println("</html>");
		}

		private void updateRow(Statement stmt, PrintWriter out, String[] uid,
				String[] uacc, String tname, String pkParam) {

			ArrayList<String> pkcols = new ArrayList<String>();
			String[] temp = pkParam.split(" and ");
			for (int i = 0; i < temp.length; i++) {
				pkcols.add(temp[i].substring(0, temp[i].indexOf("=")));
			}
			out.println("<html>");
			out.println("  <head><title>Insert");
			out.println("  </title><link type=\"text/css\" rel=\"stylesheet\" href=\"main.css\"/></head>");
			out.println("  <body>");
			out.println("    <p>");
			out.println("    <center>");

			ResultSet rset = null;
			String sql = "select * from " + tname + " where " + pkParam;
			System.out.println(sql);
			try {
				rset = stmt.executeQuery(sql);
			} catch (SQLException e) {
				e.printStackTrace();
				out.println("executeQuery " + e.getMessage());
				return;
			}

			try {
				out.println("<form action=\"/DatabaseBrowser/Update\" method=\"post\">");
				out.println("<input type=\"hidden\" name=\"uid\" value=\""
						+ uid[0] + "\">");
				out.println("<input type=\"hidden\" name=\"uAccess\" value=\""
						+ uacc[0] + "\">");
				out.println("<input type=\"hidden\" name=\"tName\" value=\""
						+ tname + "\">");
				out.println("<input type=\"hidden\" name=\"pkParam\" value=\""
						+ pkParam + "\">");
				out.println("<table border=2 id=\"table\">");

				ResultSetMetaData meta = rset.getMetaData();
				rset.next();
				for (int i = 1; i <= meta.getColumnCount(); i++) {
					out.println("<tr>");

					String temps = "";
					if (meta.getColumnTypeName(i).equals("NUMBER")) {
						if (meta.getPrecision(i) == 0)
							temps = "";
						else if (meta.getScale(i) != 0)
							temps = "(" + meta.getPrecision(i) + ","
									+ meta.getScale(i) + ")";
						else
							temps = "(" + meta.getPrecision(i) + ")";
					} else if (meta.getColumnTypeName(i).equals("DATE")) {
						temps = "";
					} else {
						temps = "(" + meta.getPrecision(i) + ")";
					}

					out.println("<td>" + meta.getColumnLabel(i) + " "
							+ meta.getColumnTypeName(i) + "" + temps + "</td>");
					out.println("<td>");
					if (pkcols.contains(meta.getColumnName(i))) {
						out.println("<input type=\"text\" name=\""
										+ meta.getColumnLabel(i)
										+ "\" value=\"" + rset.getString(i)
										+ "\" readonly=\"true\" />");
					} else {
						out.println("<input type=\"text\" name=\""
								+ meta.getColumnLabel(i) + "\" value=\""
								+ rset.getString(i) + "\" />");
					}
					out.println("</td>");
					out.println("</tr>");
				}

				out.println("</table>");

			} catch (SQLException e) {
				e.printStackTrace();
			}

			out.println("</br>");

			out.println("<input type=\"submit\" value=\"Update\">");
			out.println("</form>");

			out.println("</br>");
			out.println("<div id=\"note\">");
			out.println("</br>");
			out.println("<span>Primary keys are not supposed to be changed!</span>");
			out.println("<span>(Date format must be \"yyyy-MM-dd HH:mm:ss\". Use \"NOW\" representing current time)</span>");
			out.println("</div>");

			out.println("    </center>");
			out.println("    </p>");
			out.println("  </body>");
			out.println("</html>");
		}

	}
